﻿/*******************************
 * 
 * 数据访问层
 * 部门数据
 * 
 * 显示所有部门、添加部门信息、根据id来显示详情、修改部门的状态、删除、更新
 * Author：JXL
 * 
 * 
 ***************************************/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using IDAL;
using Model;
using System.Configuration;
using System.Data.SqlClient;


namespace DAL
{
  public  class DepartmentDAL:IDepartmentDAL
    {
     // public static string Conns = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;

      /// <summary>
      /// 显示所有部门
      /// </summary>
      /// <returns></returns>
      public List<Department> DeptShow()
      {
         using(SqlConnection conn =new SqlConnection(HouseCommon.Common.conn))
         {
             conn.Open();
             string sql = "select Department_Id,Department_Name,Department_Remarks,(case when  Department_IsState =0 then '未启用' when Department_IsState=1 then '已启用' end) as Department_IsState  from Department";
             SqlCommand cmd = new SqlCommand(sql, conn);
             SqlDataReader read = cmd.ExecuteReader();
             List<Department> list = new List<Department>();
             while (read.Read())
             {
                 Department mo = new Department();
                 mo.department_Id = Convert.ToInt32(read["Department_Id"]);
                 mo.department_IsStates = read["Department_IsState"].ToString();
                 mo.department_Remarks = read["Department_Remarks"].ToString();
                 mo.department_Name = read["Department_Name"].ToString();
                 list.Add(mo);
             }
             conn.Close();
             return list;
         }
      }


      /// <summary>
      /// 添加部门信息
      /// </summary>
      /// <param name="mo"></param>
      /// <returns></returns>
      public int DeptAdd(Department mo)
      {
          using (SqlConnection conn = new SqlConnection(HouseCommon.Common.conn))
          {
              conn.Open();
              string sql = string.Format("insert into Department(Department_Name,Department_Remarks,Department_IsState) values('{0}','{1}','{2}')",mo.department_Name,mo.department_Remarks,mo.department_IsState);
              SqlCommand cmd = new SqlCommand(sql, conn);
              int i = cmd.ExecuteNonQuery();
              conn.Close();
              return i;
          }
      }

      /// <summary>
      /// 根据id来显示详情 
      /// </summary>
      /// <param name="id"></param>
      /// <returns></returns>
      public List<Department> DeptSelectId(int id)
      {
          using (SqlConnection conn = new SqlConnection(HouseCommon.Common.conn))
          {
              conn.Open();
              string sql = string.Format("select Department_Id,Department_Name,Department_Remarks,(case when  Department_IsState =0 then '未启用' when Department_IsState=1 then '已启用' end) as Department_IsState  from Department where Department_Id='{0}'",id);
              SqlCommand cmd = new SqlCommand(sql,conn);
              SqlDataReader read = cmd.ExecuteReader();
              List<Department> list = new List<Department>();
              while (read.Read())
              {
                  Department mo = new Department();
                  mo.department_Id = Convert.ToInt32(read["Department_Id"]);
                  mo.department_IsStates = read["Department_IsState"].ToString();
                  mo.department_Remarks = read["Department_Remarks"].ToString();
                  mo.department_Name = read["Department_Name"].ToString();
                  list.Add(mo);
                  
              }
              conn.Close();
              return list;
          }
      }


      /// <summary>
      /// 将状态改为启用
      /// </summary>
      /// <param name="id"></param>
      /// <returns></returns>
      public int DeptFull(int id)
      {
          using (SqlConnection conn = new SqlConnection(HouseCommon.Common.conn))
          {
              conn.Open();
              string sql = string.Format("update Department set Department_IsState=1 where Department_Id='{0}'",id);
              SqlCommand cmd = new SqlCommand(sql, conn);
              int i = cmd.ExecuteNonQuery();
              conn.Close();
              return i;
          }
      }


      /// <summary>
      /// 将状态改为未启用
      /// </summary>
      /// <param name="id"></param>
      /// <returns></returns>
      public int DeptStop(int id)
      {
          using (SqlConnection conn = new SqlConnection(HouseCommon.Common.conn))
          {
              conn.Open();
              string sql = string.Format("update Department set Department_IsState=0 where Department_Id='{0}'", id);
              SqlCommand cmd = new SqlCommand(sql, conn);
              int i = cmd.ExecuteNonQuery();
              conn.Close();
              return i;
          }
      }

      /// <summary>
      /// 删除
      /// </summary>
      /// <param name="id"></param>
      /// <returns></returns>
      public int DeptDel(int id)
      {
          using (SqlConnection conn = new SqlConnection(HouseCommon.Common.conn))
          {
              conn.Open();
              string sql = string.Format("delete from Department where Department_Id='{0}'",id);
              SqlCommand cmd = new SqlCommand(sql, conn);
              int i = cmd.ExecuteNonQuery();
              conn.Close();
              return i;
          }
      }
      /// <summary>
      /// 更新
      /// </summary>
      /// <param name="mo"></param>
      /// <returns></returns>

      public int DeptUptFull(Department mo)
      {
          using (SqlConnection conn = new SqlConnection(HouseCommon.Common.conn))
          {
              conn.Open();
              string sql = string.Format("update Department set Department_Name='{0}',Department_Remarks='{1}' where Department_Id='{2}'",mo.department_Name,mo.department_Remarks,mo.department_Id);
              SqlCommand cmd = new SqlCommand(sql, conn);
              int i = cmd.ExecuteNonQuery();
              conn.Close();
              return i;
          }
      }
    }
}
